const { existsSync } = require('fs');

/**
 * 数据库连接初始c化
 * @param {失败func} funErr 
 * @param {成功func} funSucess 
 */
function init(funErr,funSucess){
    var mysql = require('mysql');
    var static = require('./static');

    var mysqlConn = mysql.createConnection(static.connConfig);

    mysqlConn.connect((err)=>{
        if(err) {
            funErr();
        }else{
            funSucess();
            mysqlConn.end();
        }
    })
}
/**
 * 执行sql语句
 * @param {mysql连接对象} mysqlConn 
 * @param {sql语句} sql 
 * @param {回调} callback 
 */
function excute(body,callback){
    var mysql = require('mysql');
    var static = require('./static');
    var config = static.connConfig;
    config.multipleStatements = true;//支持批量操作sql
    var mysqlConn = mysql.createConnection(config);


    var ren = {};
    ren.sheetName = body.sheetName;
    ren.code = 0;//0连接错误-1建表失败1建表成功
    ren.msg = '连接错误';

    var sql = body.sql_drop+body.sql;
    console.log(sql);

    return new Promise(((resolve, reject)=>{
        mysqlConn.connect(function(err){
        if(err){
            console.log('err:'+err);
            // callback(ren);
            resolve(ren)
        }else{
            console.log(mysqlConn.state);
            //先执行删表操作
            mysqlConn.query(sql,function(err){
                if(err){
                    console.log(err);
                    ren.code = -1;
                    ren.msg = '建表失败';   
                }else{
                    ren.code = 1;
                    ren.msg = '建表成功';
                }
                
                console.log(ren);
                // callback(ren);
                resolve(ren)
            })
        }
    })
    }))
}



/**
 * 生成建表语句
 * @param {sheet对象} sheet 
 */
function createSheet(sheet){
    var tableName = sheet.tableName;
    var tableComment = sheet.tableComment;
    var columns = sheet.columns;
    var charset = sheet.charset;


    var body = {};
    var sql_drop = `DROP TABLE IF EXISTS ${tableName};`;
    var sql = `CREATE TABLE ${tableName} (`

        columns.forEach(function(v,i){
            sql += `\`${v.name}\` `;
            var type = v.type;
            var len = v.len;
            var decimal = v.decimal;
            if(decimal == 0){
                if(type == 'varchar'){
                    sql += `${type}(${len}) `
                }else{
                    sql += `${type} `
                }
            }else{
                sql += `${type}(${len},${decimal}) `
            }
            if(v.isN == 'N'){
                sql += `NOT NULL `
            }
            if(v.defaultV == 'AUTO_INCREMENT'){
                sql += `AUTO_INCREMENT `
            }else if(v.defaultV == 'CURRENT_TIMESTAMP'){
                sql += `DEFAULT CURRENT_TIMESTAMP `
            }else if(v.defaultV == 'EMPTY_STR'){
                sql += `DEFAULT '' `
            }else{
                sql += `DEFAULT \'${v.defaultV}\' `
            }

            sql += `COMMENT \'${v.comment}\' , `
        });

        var isK = false;
        var keys = [];
        columns.forEach(function(v,i){
            if(v.isKey == 'Y'){
                isK = true;
                keys.push(v.name);
            }
        })
        if(isK){
            sql += `PRIMARY KEY (`;

            for(var i=0;i<keys.length;i++){
                if(i==0){
                    sql += `\`${keys[i]}\``
                }else{
                    sql += `,\`${keys[i]}\``
                }
            }
            sql += `)`
        }

        sql += `) ENGINE=InnoDB DEFAULT CHARSET=${charset} COMMENT=\'${tableComment}\';`
    
    // console.log(sql);
    body.sql_drop = sql_drop;
    body.sql = sql;
    body.sheetName = tableName+','+tableComment;
    return body;
}

/**
 * 将json对象转成sheet对象数组
 * @param {json对象} jsonObj 
 * @param {指定编码} charset 
 */
function toSqlSheets(jsonObj,charset){
    var sheetClazz = require('./sqlSheet');
    var sqlSheets = [];
    var charset = charset; 

    jsonObj.forEach(function(v){
        var sheetName = v.name;
        var tableName = sheetName.split(",")[0];
        var tableComment = sheetName.split(",")[1];

        var sheet = new sheetClazz.sheet(tableName,charset,tableComment);

        var columns = [];

        var datas = v.data;
        for(var i=1;i<datas.length;i++){
            var col = datas[i];
            var column = new sheetClazz.column(col[0],col[1],col[2],col[3],col[4],col[5],col[6],col[7]);
            columns.push(column);
        }
        sheet.setColumns(columns);
        sqlSheets.push(sheet);
    })

    return sqlSheets;
}

module.exports.init = init;
module.exports.excute = excute;
module.exports.createSheet = createSheet;
module.exports.toSqlSheets = toSqlSheets;